package server;
import java.sql.*;
	
  //to do something about database
public class lbs_dao  { 	
	Statement statement;
	Connection connection;
	
	//connect to the database
    public void connectToSQL() throws SQLException{
		String url="jdbc:mysql://localhost:3306/lbs";
        String user ="root";
        String password = "lbs";
        try {
        	    Class.forName("com.mysql.jdbc.Driver").newInstance();  //加载驱动器  	
            }catch(Exception e) {   e.printStackTrace();      }        
        
        try {
               connection = DriverManager.getConnection( url, user, password );            
            }catch(Exception e) {  e.printStackTrace();   }         
 	}//end of connectToSQL() 
	
	//to set the para islogin of user,1 means that the user already login
	public void setIsLogin(String name, int log){
		try{
	        String as ="update lbs_client set islogin = "+log+" where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
	}
	
	//to set the para grade of user
	public void setGrade(String name, int grade){
		try{
	        String as ="update lbs_client set grade = "+grade+" where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
	}
	
	//to set the para position of user
	public void setPosition(String name, String pos){
		try{
	        String as ="update lbs_client set position = '"+pos+"' where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
	}
	
	//to set the para groupName of user
	public void setGroupName(String name, String gname){
		try{
	        String as ="update lbs_client set groupName = '"+gname+"' where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
	}
	
	//to set the para friend of user
	public void setFriendList(String name, String friend){
		try{
	        String as ="update lbs_client set friend = '"+friend+"' where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
	}
	
	//to set the para canSeen of user
	  //0 for all
    //1 for friend only
    //2 for people in the same group
    //3 for no
    //4 need query
    //5 for friend and people in the same group
	public void setCanSeen(String name, int can){
		try{
	        String as ="update lbs_client set canSeen = "+can+" where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
	}
	
	//to set the para of canHelp of user 
	//0  for all
	//1  only for friend
	//2  only for user in the same group
	//3  for no
    //4 need query
    //5 for friend and people in the same group 
	public void setCanHelp(String name, int help){
		
		try{
	        String as ="update lbs_client set canHelp = "+help+" where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
		
	}
    
	//to set the para of setCanDisturb of user 
	//0  for all
	//1  only for friend
	//2  only for user in the same group
	//3  for no 
	 //4 need query
    //5 for friend and people in the same group
	public void setCanDisturb(String name, int CanDisturb){
		
		try{
	        String as ="update lbs_client set canDisturb = "+CanDisturb+" where name = '"+name+"'";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
	}
	
    //to insert a new user log in the table lbs_client
	public int insert(String name, int sex, String passwd){
		// for register
		// if success return 0;
		// else return 1;
		//default : grade = 0, islogin = 0, pos = null, groupname = null
		// canHelp = 0, canSeen = 0, friend = null
		try{
	        String as ="insert into lbs_client values ('"+name+"',"+sex+",0,0,'"+passwd+"', null, null,0,0,0,null)";
	        statement = connection.createStatement();
	        statement.executeUpdate(as);
	        statement.close();
	        this.setPosition(name,null);
	        this.setGroupName(name,"0");
		}catch(SQLException e){e.printStackTrace();}
		
		return 0;	
	}
	
	//to judge the name has appeared in the database or not
	//true means that the name has already appeared
	public boolean isNameUsed(String name){
		
		try{
			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery("select name  from lbs_client where name='"+name+"'");
			if (!rs.next()){
				   statement.close();
				return false;
			}
			else{
				   statement.close();
				return true;
			}
		}catch(SQLException e){e.printStackTrace();}
		return false;
	}
	
    //to judge the user name and password is match or not 
	//true means that match,that is the user is legal
	public boolean isPasswdRight(String name, String passwd){
		try{
			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery("select * from lbs_client where name='"+name+"' " +
					        "and password = '"+passwd+"'");
			if (!rs.next()){
				   statement.close();
				return false;
			}
			else{
				   statement.close();
				return true;
			}
		}catch(SQLException e){e.printStackTrace();}
		return false;
	}
	public int getSexByName(String name){
		int sex = 0;
		try{
	        String as ="select * from lbs_client where name='"+name+"'";
	   	    statement = connection.createStatement();
	        ResultSet rs = statement.executeQuery(as);
	        while(rs.next())
	          sex=rs.getInt("sex");	
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
		return sex;
	}
	public int getCanSeen(String name){
		//0  for all
		//1  only for friend
		//2  only for user in the same group
		//3  for no
	    //4 need query
	    //5 for friend and people in the same group 
		int canSeen = 0;
		try{
	        String as ="select * from lbs_client where name='"+name+"'";
	   	    statement = connection.createStatement();
	        ResultSet rs = statement.executeQuery(as);
	        while(rs.next())
	          canSeen=rs.getInt("canSeen");	
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
		return canSeen;
	}
	
	public int getCanHelp(String name){
		//0  for all
		//1  only for friend
		//2  only for user in the same group
		//3  for no
	    //4 need query
	    //5 for friend or people in the same group 
		int canHelp = 0;
		try{
	        String as ="select * from lbs_client where name='"+name+"'";
	   	    statement = connection.createStatement();
	        ResultSet rs = statement.executeQuery(as);
	        while(rs.next())
	          canHelp=rs.getInt("canHelp");	
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
		return canHelp;
	}
	
	public int getCanDisturb(String name){
		//0  for all
		//1  only for friend
		//2  only for user in the same group
		//3  for no
	    //4 need query
	    //5 for friend and people in the same group 
		int cand = 0;
		try{
	        String as ="select * from lbs_client where name='"+name+"'";
	   	    statement = connection.createStatement();
	        ResultSet rs = statement.executeQuery(as);
	        while(rs.next())
	          cand=rs.getInt("canDisturb");	 
	        statement.close();
		}catch(SQLException e){e.printStackTrace();}
		return cand;
	}
	//to return the personal information about the user
    //the format of the per_Info is
	//name sex grade islogin password position groupName canHelp canSeen canIntr
	//if the name is wrong,return null
	public String getPerInfo(String name) throws SQLException{
		//ToDo
		  String per_Info="";
		  String as ="select * from lbs_client where name='"+name+"'";
		  statement = connection.createStatement();
	      ResultSet rs = statement.executeQuery(as);
	      while(rs.next())
	    	per_Info=rs.getString("name")+"\t"
	    	        +rs.getInt("sex")+"\t"
	    	        +rs.getInt("grade")+"\t"
	    	        +rs.getInt("islogin")+"\t"
	    	        +rs.getString("password")+"\t"
	    	        +rs.getString("position")+"\t"
	    	        +rs.getString("groupName")+"\t"
	    	        +rs.getInt("canHelp")+"\t"+
	    	        +rs.getInt("canSeen")+"\t"
	                +rs.getInt("canIntr");
	      System.out.println(per_Info);
	      statement.close();
		return null;
	}
	
	//to return the para position of user
	//if the user is wrong or the  user has not check in,return null
	public String getPosByName(String name){
		try{
			String pos=null;
	        String as ="select * from lbs_client where name='"+name+"'";
	   	 statement = connection.createStatement();
	        ResultSet rs = statement.executeQuery(as);
	        while(rs.next())
	          pos=rs.getString("positon");
	        statement.close();
	        return pos;
	        
		}catch(SQLException e){e.printStackTrace();}
		return null;
	}
	
	//to return the para grade of user
	//if the user is wrong,return -1
	public int getGradeByName(String name){
		try{
		int grade=0;
        String as ="select * from lbs_client where name='"+name+"'";
    	statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(as);
        while(rs.next())
          grade=rs.getInt("grade");
        statement.close();
        return grade;
	}catch(SQLException e){e.printStackTrace();}
		return -1;
	}
	
	//to return the para groupName of user
	//if the user is wrong or the groupName is empty,return null
	public String getGroupNameByName(String name){
		try{
			String gname=null;
	        String as ="select * from lbs_client where name='"+name+"'";
	   	    statement = connection.createStatement();
	        ResultSet rs = statement.executeQuery(as);
	        while(rs.next())
	        	gname=rs.getString("groupName");
	        statement.close();
	        return gname;
		}catch(SQLException e){e.printStackTrace();}
		return null;
	}
	
	//to return the friendList of user
	//friend: name  
	public String getFriendListByName(String name){
		 String  fred=null;
	     try{

		     String as ="select * from lbs_client where name='"+name+"'";
			 statement = connection.createStatement();
	         ResultSet rs = statement.executeQuery(as);
	         while(rs.next())
	    	    fred=rs.getString("friend");
	         if(fred.isEmpty()) return null;
	         statement.close();
	       }catch(SQLException e){e.printStackTrace();}
	       return fred;
	}
	
	public String getFriendSexByName(String name){
		 String  fred=null;
		 String out = "";
	     try{
	    	 String []fris = getFriendListByName(name).split(";");
	    	
	    	 for (int i = 0; i < fris.length; i++){
			     String as ="select * from lbs_client where name='"+fris[i]+"'";
				 statement = connection.createStatement();
		         ResultSet rs = statement.executeQuery(as);
		         while(rs.next())
		    	    fred=rs.getString("sex");
		         statement.close();
		         if (i == fris.length-1){
		        	 out += fred;
		         }else{
		        	 out += fred + ";";
		         }
	    	 }
	       }catch(SQLException e){e.printStackTrace();}
	       return out;
	}
	
	
	public String getFriendPosByName(String name){
		 String  fred=null;
		 String out = "";
	     try{
	    	 String []fris = getFriendListByName(name).split(";");
	    	
	    	 for (int i = 0; i < fris.length; i++){
			     String as ="select * from lbs_client where name='"+fris[i]+"'";
				 statement = connection.createStatement();
		         ResultSet rs = statement.executeQuery(as);
		         while(rs.next())
		    	    fred=rs.getString("position");
		         statement.close();
		         if (i == fris.length-1){
		        	 out += fred;
		         }else{
		        	 out += fred + ";";
		         }
		    	 statement.close();
	    	 }
	       }catch(SQLException e){e.printStackTrace();}
	       return out;
	}
	
	public String getFriendGnameByName(String name){
		 String  fred=null;
		 String out = "";
	     try{
	    	 String []fris = getFriendListByName(name).split(";");
	    	
	    	 for (int i = 0; i < fris.length; i++){
			     String as ="select * from lbs_client where name='"+fris[i]+"'";
				 statement = connection.createStatement();
		         ResultSet rs = statement.executeQuery(as);
		         while(rs.next())
		    	    fred=rs.getString("groupName");
		         statement.close();
		         if (i == fris.length-1){
		        	 out += fred;
		         }else{
		        	 out += fred + ";";
		         }
		    	 statement.close();
	    	 }
	       }catch(SQLException e){e.printStackTrace();}
	       return out;
	}
	
	public String getFriendLoginByName(String name){
		 String  fred=null;
		 String out = "";
	     try{
	    	 String []fris = getFriendListByName(name).split(";");
	    	 
	    	 for (int i = 0; i < fris.length; i++){
			     String as ="select * from lbs_client where name='"+fris[i]+"'";
				 statement = connection.createStatement();
		         ResultSet rs = statement.executeQuery(as);
		         while(rs.next())
		    	    fred=rs.getString("islogin");
		         statement.close();
		         if (i == fris.length-1){
		        	 out += fred;
		         }else{
		        	 out += fred + ";";
		         }
		         statement.close();
	    	 }
	       }catch(SQLException e){e.printStackTrace();}
	       return out;
	}
	
	public String getFriendGradeByName(String name){
		 String  fred=null;
		 String out = "";
	     try{
	    	 String []fris = getFriendListByName(name).split(";");
	    	 
	    	 for (int i = 0; i < fris.length; i++){
			     String as ="select * from lbs_client where name='"+fris[i]+"'";
				 statement = connection.createStatement();
		         ResultSet rs = statement.executeQuery(as);
		         while(rs.next())
		    	    fred=rs.getString("grade");
		         if (i == fris.length-1){
		        	 out += fred;
		         }else{
		        	 out += fred + ";";
		         }
		    	 statement.close();
	    	 }
	       }catch(SQLException e){e.printStackTrace();}
	       return out;
	}
	//to return the Member of group by the groupName
	//Member:name sex grade
	//if the group is empty,return null
	public String getGroupMemByGroupName(String groupName){
		 String  getGroupMem="";		 
	     try{
	    	 statement = connection.createStatement();
		     String as ="select * from lbs_client where groupName='"+groupName+"'";
	         ResultSet rs = statement.executeQuery(as);
	         while(rs.next())
	         {
	        	 getGroupMem+=";"+rs.getString("name");
	         }
	     	statement.close();
	       }catch(SQLException e){e.printStackTrace();}
	    if( getGroupMem.isEmpty())
		   return null;
	    else 
	    	return getGroupMem.substring(1);
	}
	public void clear()
	{
		String as ="select * from lbs_client";
        try {
        	statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(as);
			while(rs.next())
			{
				String uname = rs.getString("name");
				this.setGrade(uname, 0);
				this.setGroupName(uname,"0");
				this.setIsLogin(uname,0);
				this.setPosition(uname, null);
				this.setCanHelp(uname,1);
				this.setCanDisturb(uname, 1);
				this.setCanSeen(uname, 1);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
} 


